package mapper;

import entity.BusEntity;
import entity.UserBusDto;
import entity.UserBusMappingEntity;
import util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author HuangJH
 * @create 2023-11-30-22:07
 */
public class UserBusMappingMapper {

    /**
     * 查询预约的班车
     * @param userId
     * @return
     */
    public List<UserBusDto> getAllBus(Integer userId) {
        //声明JDBC 中需要用到的几个变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        List<UserBusDto> userBusDtos = new ArrayList<>();
        try {
            connection = JDBCUtils.getConnection();
            String sql = "select t1.*,t3.bus_name,t3.departure_station,t3.terminal from user_bus_mapping t1 " +
                    "left join bus t3 on t3.id = t1.bus_id " +
                    "where t1.user_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, userId);

            set = preparedStatement.executeQuery();
            while (set.next()) {
                UserBusDto userBusDto = new UserBusDto();
                BusEntity busEntity = new BusEntity();
                UserBusMappingEntity userBusMappingEntity = new UserBusMappingEntity();
                userBusMappingEntity.setId(set.getInt("id"));
                userBusMappingEntity.setUserId(set.getInt("user_id"));
                userBusMappingEntity.setBusId(set.getInt("bus_id"));
                userBusMappingEntity.setAppointmentTime(set.getString("appointment_time"));
                userBusDto.setUserBusMappingEntity(userBusMappingEntity);
                busEntity.setId(set.getInt("bus_id"));
                busEntity.setBusName(set.getString("bus_name"));
                busEntity.setDepartureStation(set.getString("departure_station"));
                busEntity.setTerminal(set.getString("terminal"));
                userBusDto.setBusEntity(busEntity);
                userBusDtos.add(userBusDto);
            }
        } catch (Exception e) {
            System.out.println("查询所有已预约班车异常");
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }
        return userBusDtos;
    }

    /**
     * 预约班车
     * @param userBusMappingEntity
     * @return
     */
    public boolean insert(UserBusMappingEntity userBusMappingEntity) {
        //声明JDBC 中需要用到的几个变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        int successNum = 0;
        try {
            connection = JDBCUtils.getConnection();
            String sql = "insert into user_bus_mapping(user_id, bus_id, appointment_time) values(?, ?, ?);";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, userBusMappingEntity.getUserId());
            preparedStatement.setInt(2, userBusMappingEntity.getBusId());
            preparedStatement.setString(3, userBusMappingEntity.getAppointmentTime());
            successNum = preparedStatement.executeUpdate();
        } catch (Exception e) {
            System.out.println("预约班车异常");
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }
        if (successNum > 0) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 解约班车
     * @param id
     * @return
     */
    public boolean delete(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        int successNum = 0;
        try {
            connection = JDBCUtils.getConnection();
            String sql = "delete from user_bus_mapping where id = ?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            successNum = preparedStatement.executeUpdate();
        } catch (Exception e) {
            System.out.println("解约班车异常");
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }
        if (successNum > 0) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 条件查询班车
     * @param mappingNum
     * @return
     */
    public List<UserBusDto> getById(int mappingNum) {
        //声明JDBC 中需要用到的几个变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        List<UserBusDto> userBusDtos = new ArrayList<>();
        try {
            connection = JDBCUtils.getConnection();
            String sql = "select t1.*,t3.bus_name,t3.departure_station,t3.terminal from user_bus_mapping t1 " +
                    "left join bus t3 on t3.id = t1.bus_id " +
                    "where t1.id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, mappingNum);

            set = preparedStatement.executeQuery();
            while (set.next()) {
                UserBusDto userBusDto = new UserBusDto();
                BusEntity busEntity = new BusEntity();
                UserBusMappingEntity userBusMappingEntity = new UserBusMappingEntity();
                userBusMappingEntity.setId(set.getInt("id"));
                userBusMappingEntity.setUserId(set.getInt("user_id"));
                userBusMappingEntity.setBusId(set.getInt("bus_id"));
                userBusMappingEntity.setAppointmentTime(set.getString("appointment_time"));
                userBusDto.setUserBusMappingEntity(userBusMappingEntity);
                busEntity.setId(set.getInt("bus_id"));
                busEntity.setBusName(set.getString("bus_name"));
                busEntity.setDepartureStation(set.getString("departure_station"));
                busEntity.setTerminal(set.getString("terminal"));
                userBusDto.setBusEntity(busEntity);
                userBusDtos.add(userBusDto);
            }
        } catch (Exception e) {
            System.out.println("查询所有已预约班车异常");
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }
        return userBusDtos;
    }
}
